﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using WinNemoLib;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace ToppingControl.Models
{
   public class CompoundUseModel
   {
      public class CompoundUse_DS
      {
         public string _Topno { get; set; }
         public string _Compdate { get; set; }
         public string _Compused { get; set; }
         public string _Compcd { get; set; }
         public string _Empname { get; set; }
         public string _Empdt { get; set; }
         public string _Complot { get; set; }
         public string _Shift { get; set; }
      }

      public List<CompoundUse_DS> SEL_TOP_COMPUSED(string dt)
      {

         string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
         SqlConnection cnn = new SqlConnection(ConnectionString);
         StringBuilder str = new StringBuilder();

         str.AppendLine(" select compcd,sum(compused) compused,complot,shift from " + Nemo.GetINI("Dbase", "Base") + ".dbo.TOP_COMPUSED ");
         str.AppendLine(" where compdate=convert(datetime,@dt,120)  group by compcd,complot,shift ");

         SqlCommand cmd = new SqlCommand(str.ToString(), cnn);
         cmd.Connection.Open();
         cmd.CommandType = CommandType.Text;
        cmd.Parameters.Add("@dt", SqlDbType.VarChar).Value = DateUtil.ToInternalDate(dt, WinNemoLib.DateUtil.eLanguage.EN);


         SqlDataAdapter my = new SqlDataAdapter();
         DataSet ds = new DataSet();
         my.SelectCommand = cmd;
         my.Fill(ds);

         List<CompoundUse_DS> list = new List<CompoundUse_DS>();
         if (ds.Tables[0].Rows.Count <= 0)
         {
            cmd.Connection.Close();
            return list;
         }
         for (int cuIndex = 0; cuIndex < ds.Tables[0].Rows.Count; cuIndex++)
         {
            DataRow items = ds.Tables[0].Rows[cuIndex];
            CompoundUse_DS model = new CompoundUse_DS();
            //model._Topno = items["topno"].ToString();
            model._Shift = items["shift"].ToString();
            model._Compused = items["compused"].ToString();
            model._Compcd = items["compcd"].ToString();
            model._Complot = items["complot"].ToString();
            list.Add(model);
         }
         return list;
      }

      public List<CompoundUse_DS> LIST_COMPUSED(string prdF="", string prdT="")
      {

         string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
         SqlConnection cnn = new SqlConnection(ConnectionString);
         StringBuilder str = new StringBuilder();

         str.AppendLine(" select * from " + Nemo.GetINI("Dbase", "Base") + ".dbo.TOP_COMPUSED ");
         string where = "";

         if ((StrUtil.IsNotEmpty(prdF) && StrUtil.IsNotEmpty(prdT)))
         {
             where = " where compdate between convert(datetime,@dtF,120) and convert(datetime,@dtT,120) ";
         }
         else if (StrUtil.IsNotEmpty(prdF))
         {
             where = " where compdate=convert(datetime,@dtF,120) ";
         }


         SqlCommand cmd = new SqlCommand(str.ToString()+where, cnn);
         cmd.Connection.Open();
         cmd.CommandType = CommandType.Text;

         if (StrUtil.IsNotEmpty(prdF))
         {
             cmd.Parameters.Add("@dtF", SqlDbType.VarChar).Value = DateUtil.ToInternalDate(prdF, WinNemoLib.DateUtil.eLanguage.EN);
         }

         if (StrUtil.IsNotEmpty(prdT))
         {
             cmd.Parameters.Add("@dtT", SqlDbType.VarChar).Value = DateUtil.ToInternalDate(prdT, WinNemoLib.DateUtil.eLanguage.EN);
         }

         SqlDataAdapter my = new SqlDataAdapter();
         DataSet ds = new DataSet();
         my.SelectCommand = cmd;
         my.Fill(ds);

         List<CompoundUse_DS> list = new List<CompoundUse_DS>();
         if (ds.Tables[0].Rows.Count <= 0)
         {
            cmd.Connection.Close();
            return list;
         }
         for (int cuIndex = 0; cuIndex < ds.Tables[0].Rows.Count; cuIndex++)
         {
            DataRow items = ds.Tables[0].Rows[cuIndex];
            CompoundUse_DS model = new CompoundUse_DS();
            model._Topno = items["topno"].ToString();
            model._Compdate = DateUtil.ToDisplayDate(items["compdate"], DateUtil.eLanguage.EN);
            model._Compused = items["compused"].ToString();
            model._Compcd = items["compcd"].ToString();
            model._Empname = items["empname"].ToString();
            model._Empdt = DateUtil.ToDisplayDateTime(items["empdate"], DateUtil.eLanguage.EN);
            model._Shift = items["shift"].ToString();
            model._Complot = items["complot"].ToString();
            list.Add(model);
         }
         return list;
      }

      public Boolean Ins_Data(CompoundUse_DS incoming)
      {
         try
         {
                  using (ConnectionWrapper cnn = Nemo.GetDefaultConfiguredDbConnectionWrapper())
            {
               cnn.Open();
               cnn.BeginTransaction();
            StringBuilder comstr = new StringBuilder();
            comstr.AppendLine(" INSERT INTO ");
            comstr.AppendLine(Nemo.GetINI("Dbase", "Base") + ".[dbo].[TOP_COMPUSED]");
            comstr.AppendLine(" ([topno], [compdate], [compused], [compcd], [empname],empdate,complot,shift) ");
            comstr.AppendLine("  VALUES (@topno,convert(datetime, @compdate,120), @compused, @compcd, @empname,getdate(),@complot,@shift)");


            CommandWrapper cmd = cnn.CreateCommand();
            cmd.CommandText = comstr.ToString();



            cmd.AddParameterStringWithValue("@compdate", DateUtil.ToInternalDate(incoming._Compdate, WinNemoLib.DateUtil.eLanguage.EN));
            cmd.AddParameterStringWithValue("@topno", incoming._Topno);
            cmd.AddParameterStringWithValue("@compused", incoming._Compused.Replace(",",""));
            cmd.AddParameterStringWithValue("@compcd", incoming._Compcd);
            cmd.AddParameterStringWithValue("@empname", incoming._Empname);
            cmd.AddParameterStringWithValue("@complot", incoming._Complot);
            cmd.AddParameterStringWithValue("@shift", incoming._Shift);


            if (cmd.ExecuteNonQuery() <= 0)
            {
               cnn.RollbackTransaction();
               return false;
            }
            cnn.CommitTransaction();
            return true;
           }
        
         }
         catch (Exception)
         {
            return false;
          
         }

      }

      public Boolean Ins_CPD_CH(TopCordPlanModel.TopCordPlan_DS incoming, string rollno)
      {
          try
          {
              using (ConnectionWrapper cnn = Nemo.GetDefaultConfiguredDbConnectionWrapper())
              {
                  cnn.Open();
                  cnn.BeginTransaction();
                  StringBuilder comstr = new StringBuilder();
                  comstr.AppendLine(" INSERT INTO ");
                  comstr.AppendLine(Nemo.GetINI("Dbase", "Base") + ".[dbo].[TOPCORD_CMPCHG]");
                  comstr.AppendLine(" ([topno], [plandt], [itemno], [Compcd], [Complot], [Comppllt], [Compexp], [compw], [Cauthemp], [creatxt], [cshtxt],rollno ");
                  comstr.AppendLine(" )VALUES(@topno,convert(datetime,@plandt,120) ,@itemno, @Compcd, @Complot, @Comppllt, convert(datetime,@Compexp,120), @compw, @Cauthemp, @creatxt, @cshtxt,@rollno)");


                  CommandWrapper cmd = cnn.CreateCommand();
                  cmd.CommandText = comstr.ToString();



                  cmd.AddParameterStringWithValue("@plandt", DateUtil.ToInternalDate(incoming._Plandt, WinNemoLib.DateUtil.eLanguage.EN));
                  cmd.AddParameterStringWithValue("@topno", incoming._Topno);
                  cmd.AddParameterStringWithValue("@itemno", incoming._Itemno);
                  cmd.AddParameterStringWithValue("@compcd", incoming._Compcd);
                  cmd.AddParameterStringWithValue("@Complot", incoming._Complot);
                  cmd.AddParameterStringWithValue("@Comppllt", incoming._Comppllt);
                  cmd.AddParameterStringWithValue("@Compexp", DateUtil.ToInternalDate(incoming._Compexp, WinNemoLib.DateUtil.eLanguage.EN));
                  cmd.AddParameterStringWithValue("@compw", incoming._Compw);
                  cmd.AddParameterStringWithValue("@Cauthemp", incoming._Cauthemp);
                  cmd.AddParameterStringWithValue("@creatxt", incoming._Creatxt);
                  cmd.AddParameterStringWithValue("@cshtxt", incoming._Cshtxt);
                  cmd.AddParameterStringWithValue("@rollno", rollno);

                  if (cmd.ExecuteNonQuery() <= 0)
                  {
                      cnn.RollbackTransaction();
                      return false;
                  }
                  cnn.CommitTransaction();
                  return true;
              }

          }
          catch (Exception)
          {
              return false;

          }

      }

      public void GET_CPD_CH(ref TopCordActModel.TopCordAct_DS model)
      {
          string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
          SqlConnection cnn = new SqlConnection(ConnectionString);
          StringBuilder str = new StringBuilder();

          str.AppendLine(" select top 1 [topno], [plandt], [itemno], [Compcd], [Complot], [Comppllt], [Compexp], [compw], [Cauthemp], [creatxt], [cshtxt] from " + Nemo.GetINI("Dbase", "Base") + ".dbo.TOPCORD_CMPCHG");
          str.AppendLine(" where plandt=convert(datetime,@dt,120) and itemno=@itemno and convert(int,rollno)<=@roll");
          str.AppendLine(" order by convert(int,rollno) desc ");
          SqlCommand cmd = new SqlCommand(str.ToString(), cnn);
          cmd.Connection.Open();
          cmd.CommandType = CommandType.Text;
          cmd.Parameters.Add("@dt", SqlDbType.VarChar).Value = DateUtil.ToInternalDate(model._Plandt, WinNemoLib.DateUtil.eLanguage.EN);
          cmd.Parameters.Add("@itemno", SqlDbType.VarChar).Value = model._Itemno;
          cmd.Parameters.Add("@roll", SqlDbType.VarChar).Value = model._Rollno;

          SqlDataAdapter my = new SqlDataAdapter();
          DataSet ds = new DataSet();
          my.SelectCommand = cmd;
          my.Fill(ds);

          List<CompoundUse_DS> list = new List<CompoundUse_DS>();
          if (ds.Tables[0].Rows.Count <= 0)
          {
              cmd.Connection.Close();

          }
          else
          {
              DataRow items = ds.Tables[0].Rows[0];
              model._ComExp = DateUtil.ToDisplayDate(items["Compexp"], DateUtil.eLanguage.EN);
              model._Compound = items["Compcd"].ToString();
              model._ComPlt = items["Comppllt"].ToString();
              model._ComLot = items["Complot"].ToString();
              
          }

      }
   
   }
}